package com.hdsx.jxcsxm.utile;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.hdsx.jxcsxm.tjbb.bean.ExcelData;
import com.hdsx.jxcsxm.tjbb.bean.Excel_list;
import com.hdsx.jxcsxm.tjbb.bean.Excel_tilte;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.codehaus.jackson.JsonParseException;
import org.codehaus.jackson.map.JsonMappingException;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author 占贤银
 * time 2023/11/21 10:57
 * description 生命之灯因热情而点燃，生命之舟因拼搏而前行
 */
public final class ExcelExportUtil {

    /**
     * excel样式
     * @param wb
     * @return
     */
    public static Map<String,Object> excelStyle(HSSFWorkbook wb){
        HSSFCellStyle style = wb.createCellStyle();
        HSSFCellStyle style1 = wb.createCellStyle();
        HSSFCellStyle style2 = wb.createCellStyle();
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style1.setWrapText(true);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font = wb.createFont();
        HSSFFont font1 = wb.createFont();
        font.setFontName("楷体");
        font.setFontHeightInPoints((short) 18);// 设置字体大小
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 10);// 设置字体大小

        style.setFont(font1);
        style2.setFont(font);

        Map<String,Object> map = new HashMap<String,Object>();
        map.put("style", style);
        map.put("style1", style1);
        map.put("style2", style2);
        map.put("font", font);
        map.put("font1", font1);
        return map;

    }

    public static void export(List<ExcelData> list, HttpServletResponse response)
            throws Exception {
        try {
            response.setContentType("octets/stream");
            response.addHeader("Content-Disposition", "attachment;filename="
                    + new String(list.get(0).getFileName().trim().getBytes("gb2312"), "ISO-8859-1") + ".xls");
            OutputStream out = response.getOutputStream();
            HSSFWorkbook wb = new HSSFWorkbook();
            for(int i=0;i<list.size();i++) {
                generateSheet(wb,list.get(i));
            }
            wb.write(out);
            out.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成sheet
     * @param wb
     * @param el
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws InvocationTargetException
     * @throws NoSuchMethodException
     * @throws SecurityException
     */
    public static void generateSheet(HSSFWorkbook wb,ExcelData el)
            throws IllegalAccessException, IllegalArgumentException,
            InvocationTargetException, NoSuchMethodException, SecurityException {
        HSSFSheet sheet = wb.createSheet(el.getSheetName());
        HSSFRow row = sheet.createRow((int) 0);
        Map<String,Object> mapStyle = excelStyle(wb);
        HSSFCellStyle style1 = (HSSFCellStyle)mapStyle.get("style1");
        HSSFCellStyle style2 = (HSSFCellStyle)mapStyle.get("style2");
        HSSFCellStyle style = (HSSFCellStyle)mapStyle.get("style");
        HSSFCell cell = row.createCell((short) 0);
        row.setHeightInPoints(30f);
        cell.setCellValue(el.getTitleName());
        cell.setCellStyle(style2);

        List<Excel_tilte> et = el.getEt();
        int maxy = 0;
        int maxx = 0;
        int minx = 1;
        row = sheet.createRow((int) 1);
        if(et!=null && et.size() > 0) {
            for (Excel_tilte excel_tilte : et) {
                row.setHeightInPoints(20f);
                int x1 = excel_tilte.getX1();
                int x2 = excel_tilte.getX2();
                int y1 = excel_tilte.getY1();
                int y2 = excel_tilte.getY2();
                maxy = y2 > maxy ? y2 : maxy;
                maxx = x2 > maxx ? x2 : maxx;

                if (x1 > minx) {
                    minx = x1;
                    row = sheet.createRow((int) x1);
                }
                cell = row.createCell((short) y1);
                cell.setCellValue(excel_tilte.getName());
                cell.setCellStyle(style1);
            }
            for (int i = 0; i <= maxy; i++) {
//				sheet.setColumnWidth(i, 32 * 150);// 设置固定宽度150
                sheet.setColumnWidth(i, 32 * 150);
            }
            for (Excel_tilte excel_tilte : et) {
                int x1 = excel_tilte.getX1();
                int x2 = excel_tilte.getX2();
                int y1 = excel_tilte.getY1();
                int y2 = excel_tilte.getY2();
                if (x1 != x2 || y1 != y2) {
                    CellRangeAddress range = new CellRangeAddress(x1, x2, y1, y2);
                    sheet.addMergedRegion(range);
                    setRegionStyle(style1, range, sheet);
                }
            }
            CellRangeAddress range = new CellRangeAddress(0, 0, 0, maxy);
            sheet.addMergedRegion(range);
            setRegionStyle(style2, range, sheet);
            handleExcelList(el.getEl(),et,el,row,cell,maxx,sheet,style);
            handleSjbbMessage(el.getEl1(),et,el,row,cell,maxx,sheet,style);
            handleJhtzExcel(el.getJhtzList(),et,el,row,cell,maxx,sheet,style);

        }
    }
    public static void handleExcelList(List<Excel_list> dataList,
                                       List<Excel_tilte> et, ExcelData el,
                                       HSSFRow row, HSSFCell cell, int maxx,
                                       HSSFSheet sheet, HSSFCellStyle style)
            throws IllegalAccessException, IllegalArgumentException, InvocationTargetException,
            NoSuchMethodException, SecurityException {
        if(dataList!=null && dataList.size() >0 ) {
            Excel_list enity = null;
            for (int i = 0; i < dataList.size(); i++) {
                int rowNum = i + maxx + 1; // 行号
                row = sheet.createRow(rowNum);
                row.setHeightInPoints(20f);
                enity = dataList.get(i);
                Class clazz = enity.getClass();
                for(int k=0;k<et.size();k++) {
                    Excel_tilte tl =et.get(k);
                    if (tl.getY1() == tl.getY2()) {
                        cell = row.createCell(tl.getY1());
                        String field = tl.getField();
                        String val = (StringUtils.isEmpty(field) || field.length() == 0) ? "" : clazz.getMethod(generateGetMethodName(field)).invoke(enity)+"";
                        cell.setCellValue(val==null || "null".equals(val) ? "" : val);
                        cell.setCellStyle(style);
                    }
                }
            }
        }
    }

    public static void handleSjbbMessage(List<SjbbMessage> dataList,
                                         List<Excel_tilte> et, ExcelData el, HSSFRow row,
                                         HSSFCell cell, int maxx, HSSFSheet sheet,
                                         HSSFCellStyle style) throws IllegalAccessException,
            IllegalArgumentException, InvocationTargetException, NoSuchMethodException,
            SecurityException {
        if(dataList!=null && dataList.size() >0 ) {
            SjbbMessage enity = null;
            for (int i = 0; i < dataList.size(); i++) {
                int rowNum = i + maxx + 1; // 行号
                row = sheet.createRow(rowNum);
                row.setHeightInPoints(20f);
                enity = dataList.get(i);
                Class clazz = enity.getClass();
                for(int k=0;k<et.size();k++) {
                    Excel_tilte tl =et.get(k);
                    if (tl.getY1() == tl.getY2()) {
                        cell = row.createCell(tl.getY1());
                        String field = tl.getField();
                        String val = (StringUtils.isEmpty(field) || field.length() == 0) ? "" : clazz.getMethod(generateGetMethodName(field)).invoke(enity)+"";
                        cell.setCellValue(val==null || "null".equals(val) ? "" : val);
                        cell.setCellStyle(style);
                    }
                }
                if(StringUtils.isNotEmpty(enity.getHbJsonArr())) {
                    JSONArray arr = JSONArray.parseArray(enity.getHbJsonArr());
                    for(int j=0;j<arr.size();j++) {
                        JSONObject json = arr.getJSONObject(j);
                        CellRangeAddress range2 = new CellRangeAddress(rowNum, rowNum, json.getIntValue("start"), json.getIntValue("end")); sheet.addMergedRegion(range2);
                    }
                }
            }
        }
    }

    //处理计划调整列表数据
    public static void handleJhtzExcel(List<JhtzExcel> dataList,List<Excel_tilte> et,ExcelData el,
                                       HSSFRow row,HSSFCell cell,int maxx,HSSFSheet sheet,
                                       HSSFCellStyle style) throws IllegalAccessException,
            IllegalArgumentException, InvocationTargetException, NoSuchMethodException,
            SecurityException {
        if(dataList!=null && dataList.size() >0 ) {
            JhtzExcel enity = null;
            for (int i = 0; i < dataList.size(); i++) {
                int rowNum = i + maxx + 1; // 行号
                row = sheet.createRow(rowNum);
                row.setHeightInPoints(20f);
                enity = dataList.get(i);
                Class clazz = enity.getClass();
                for(int k=0;k<et.size();k++) {
                    Excel_tilte tl =et.get(k);
                    if (tl.getY1() == tl.getY2()) {
                        cell = row.createCell(tl.getY1());
                        String field = tl.getField();
                        String val = (StringUtils.isEmpty(field) || field.length() == 0) ? "" : clazz.getMethod(generateGetMethodName(field)).invoke(enity)+"";
                        cell.setCellValue(val==null || "null".equals(val) ? "" : val);
                        cell.setCellStyle(style);
                    }
                }

                if("hbtj".equals(enity.getRow_type())) {
                    CellRangeAddress range2 = new CellRangeAddress(rowNum, rowNum, 0, 3);
                    sheet.addMergedRegion(range2);
                }else if("xmcz".equals(enity.getRow_type())){
                    CellRangeAddress range2 = new CellRangeAddress(rowNum, rowNum, 1, 3);
                    sheet.addMergedRegion(range2);
                }
            }
        }
    }


    private static String generateGetMethodName(String field) {
        if(StringUtils.isNotEmpty(field)) {
            return "get"+field.substring(0,1).toUpperCase() +field.substring(1);
        }
        return "";
    }

    /**
     * 设置合并单元格格式
     *
     * @param sheet
     * @param region
     * @param cs
     */
    private static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = sheet.getRow(i);
            if (row == null)
                row = sheet.createRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);
            }
        }
    }

    /**
     * 通过JSON文件生成，excel表头
     * @param titleJsonPath
     * @return
     * @throws JsonParseException
     * @throws JsonMappingException
     * @throws IOException
     */
    public static List<Excel_tilte> generateExcelTitle(String titleJsonPath,String key) throws JsonParseException, JsonMappingException, IOException{
        ClassPathResource resource = new ClassPathResource(titleJsonPath);
        File filePath = resource.getFile();
        JSONArray btnArray = null;
        //读取文件
        String input = FileUtils.readFileToString(filePath, "UTF-8");
        //将读取的数据转换为JSONObject
        JSONObject jsonObject = JSONObject.parseObject(input);
        btnArray = jsonObject.getJSONArray(key);

        List<Excel_tilte> et = new ArrayList<Excel_tilte>();
        int start=0,end=0;
        //二级标题
        JSONArray twoArr = new JSONArray();
        for(int i=0;i<btnArray.size();i++) {
            JSONObject one = btnArray.getJSONObject(i);
            JSONArray oneChild = one.getJSONArray("child");
            if(oneChild == null || oneChild.size()==0) {
                et.add(new Excel_tilte(one.getString("txt"), 1, 2, start, end,one.getString("field")));
                start = end+1;
                end = end+1;
            }else {
                // 处理需合并单元格的元素
                JSONObject newOn = new JSONObject();
                newOn.put("start", start);
                newOn.put("data", oneChild);
                twoArr.add(newOn);

                end = start+oneChild.size()-1;
                et.add(new Excel_tilte(one.getString("txt"), 1, 1, start, end));
                start = end+1;
                end = end+1;
            }
        }

        for(int j=0;j<twoArr.size();j++) {
            JSONObject two = twoArr.getJSONObject(j);
            Integer st = two.getInteger("start");
            JSONArray childArr = two.getJSONArray("data");
            for(int k=0;k<childArr.size();k++) {
                et.add(new Excel_tilte(childArr.getJSONObject(k).getString("txt"), 2, 2, st, st,childArr.getJSONObject(k).getString("field")));
                st ++;
            }
        }
        return et;
    }

    /**
     * 超过2层的json,转换title
     * @param titleJsonPath
     * @param key
     * @return
     * @throws JsonParseException
     * @throws JsonMappingException
     * @throws IOException
     */
    public static List<Excel_tilte> generateExcelTitleMultiLevel(String titleJsonPath,String key) throws JsonParseException, JsonMappingException, IOException{
        ClassPathResource resource = new ClassPathResource(titleJsonPath);
        File filePath = resource.getFile();
        JSONArray btnArray = null;
        //读取文件
        String input = FileUtils.readFileToString(filePath, "UTF-8");
        //将读取的数据转换为JSONObject
        JSONObject jsonObject = JSONObject.parseObject(input);
        btnArray = jsonObject.getJSONArray(key);

        // 处理json数组，添加必要数据
        JSONObject newJson = new JSONObject();
        newJson = handlJsonArr(btnArray,1,1,0,newJson);
        List<Excel_tilte> et = new ArrayList<Excel_tilte>();
        for(int i=1;i<5;i++) {
            JSONArray arr =  newJson.getJSONArray("level_"+i);
            if(arr!=null&&arr.size()>0) {
                et.addAll(excelTitleList(arr));
            }
        }
        return et;
    }

    // 处理jon数组
    public static JSONObject handlJsonArr(JSONArray jsonArr,int level,Integer rowStart,Integer colStart,JSONObject result) {
        Integer rowEnd=rowStart,colEnd=colStart;
        JSONArray levelArr =  result.getJSONArray("level_"+level);
        if(levelArr==null) {
            levelArr = new JSONArray();
        }
        for(int j=0;j<jsonArr.size();j++) {
            JSONObject obj = jsonArr.getJSONObject(j);
            Integer rowspan = obj.getInteger("rowspan");
            Integer colspan = obj.getInteger("colspan");
            rowEnd = rowspan!=null ?  rowStart + rowspan - 1 : rowStart;
            colEnd = colspan!=null ? colStart + colspan - 1 : colEnd;
            obj.put("rowStart", rowStart);
            obj.put("rowEnd", rowEnd);
            obj.put("colStart", colStart);
            obj.put("colEnd", colEnd);
            obj.put("level", level);
            JSONArray childs = obj.getJSONArray("child");
            if(childs!=null && childs.size()>0) {
                handlJsonArr(childs,level+1, rowStart+1, colStart,result);
            }
            colStart = colEnd+1;
            colEnd = colEnd+1;
            obj.remove("child");
            levelArr.add(obj);
        }
        result.put("level_"+level, levelArr);
        return result;
    }


    public static List<Excel_tilte> excelTitleList(JSONArray jsonArr){
        List<Excel_tilte> et = new ArrayList<Excel_tilte>();
        if(jsonArr!=null && jsonArr.size()>0) {
            for(int j=0;j<jsonArr.size();j++) {
                JSONObject obj = jsonArr.getJSONObject(j);
                Integer rowStart = obj.getInteger("rowStart");
                Integer rowEnd = obj.getInteger("rowEnd");;
                Integer colStart = obj.getInteger("colStart");
                Integer colEnd = obj.getInteger("colEnd");;
                et.add(new Excel_tilte(obj.getString("txt"), rowStart, rowEnd, colStart, colEnd,obj.getString("field")));
            }
        }
        return et;
    }
}
